if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_AddSqlUserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_AddSqlUserLogin]
GO

PRINT 'Creating sp_AddSqlUserLogin...'
GO

CREATE PROCEDURE dbo.sp_AddSqlUserLogin
(
	@user_name sysname,
	@password sysname,
	@role_name sysname,
	@default_database sysname
)

AS

-- Try to remove the user first

-- First see if the user is in the master database
IF EXISTS (SELECT 1 FROM master.dbo.syslogins WHERE [name] = @user_name)
-- They are in master.  Now see if the user is in the current database.
BEGIN
	IF EXISTS (SELECT 1 FROM dbo.sysusers WHERE [name] = @user_name)
	BEGIN
		-- Remove the login from the current database
		EXEC sp_revokedbaccess @name_in_db = @user_name
	END
	-- Remove the login from the master database
	EXEC sp_droplogin @loginame = @user_name
	
END

-- Now try to add the user to the current database

-- Check return code to see if it is 0 (success)
DECLARE @return_code int
EXEC @return_code = sp_addlogin @loginame = @user_name,
	@passwd = @password,
	@defdb = @default_database

	-- The user was successfully added to the master database.  Now try to add the user to the 
	-- current database and assign to the specified role.
	EXEC @return_code = sp_adduser @loginame = @user_name, @grpname = @role_name

return 0
GO